﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NUnit.Framework;

namespace SwormTests
{
    public enum DbToTest
    {
        Sqlite,
        Postgres,
        SqlServer,
        MySql,
    }

    [TestFixture(DbToTest.Sqlite)]
    [TestFixture(DbToTest.Postgres)]
    [TestFixture(DbToTest.SqlServer)]
    [TestFixture(DbToTest.MySql)]
    public abstract class TestBase
    {
        protected readonly DbToTest _dbType;
        protected TestBase(DbToTest type)
        {
            this._dbType = type;
        }

        protected virtual bool IsDisabled(DbToTest type) { return false; }

        protected System.Data.IDbConnection Connection { get; private set; }
        private Sworm.SqlDialect dialect;

        protected Sworm.MetadataBuilder MetadataBuider { get; private set; }

        protected virtual Sworm.TypeAnalyzer CreateTypeAnalyzer()
        {
            return new Sworm.TypeAnalyzer();
        }

        private Sworm.MetadataBuilder CreateMetadataBuilder(Sworm.SqlDialect dialect)
        {
            return new Sworm.MetadataBuilder(dialect, CreateTypeAnalyzer());
            // this should be a singleton in production scenarios, but for testing,
            // we want a new one on each run, otherwise errors can get missed
        }

        [SetUp]
        public void _BaseSetup()
        {
            if (IsDisabled(_dbType))
            {
                Assert.Inconclusive("this test does not support dbType: " + _dbType);
            }

            string initialSql = null;

            if (_dbType == DbToTest.Sqlite)
            {
                Connection = new System.Data.SQLite.SQLiteConnection("Data Source=:memory:");
                dialect = new Sworm.SqliteDialect();
            }
            else if (_dbType == DbToTest.Postgres)
            {
                Connection = new Npgsql.NpgsqlConnection(
                    "Server=127.0.0.1;Port=5432;Userid=swormTestRole;Password=swormTestRole;Database=swormTestDB");
                dialect = new Sworm.PostgresDialect();
            }
            else if (_dbType == DbToTest.SqlServer)
            {
                Connection = new System.Data.SqlClient.SqlConnection(
                    "Server=.; User Id=swormTestLogin; Password=swormTestLogin;");
                dialect = new Sworm.SqlServerDialect();
                initialSql = @"EXEC sp_MSforeachtable @command1 = ""DROP TABLE ?""";
            }
            else if (_dbType == DbToTest.MySql)
            {
                Connection = new MySql.Data.MySqlClient.MySqlConnection(
                    "Server=localhost; Database=swormTest; Uid=swormTest; Password=swormTest");
                dialect = new Sworm.MySqlDialect();
                initialSql = "SET sql_mode = 'ANSI_QUOTES';";
            }

            MetadataBuider = CreateMetadataBuilder(dialect);

            Connection.Open();

            DB = new Sworm.Database(Connection, MetadataBuider);

            if (initialSql != null)
            {
                DB.Execute(initialSql);
            }

            // automatically recreate nested types as tables if possible
            if (ShouldRecreateTablesAutomatically)
            {
                foreach (var type in this.GetType().GetNestedTypes(
                    System.Reflection.BindingFlags.NonPublic | System.Reflection.BindingFlags.Public))
                {
                    if (char.IsLower(type.Name[0])
                        && MetadataBuider.ShouldMapTable(type))
                    {
                        RecreateTable(type);
                    }
                }
            }

            Initialize();
        }

        protected virtual bool ShouldRecreateTablesAutomatically { get { return true; } }

        protected virtual void Initialize() { }

        protected Sworm.Database DB { get; private set; }

        [TearDown]
        public void _BaseTearDown()
        {
            if (Connection != null)
            {
                Connection.Close();
                Connection.Dispose();
                Connection = null;
            }
        }

        protected void RecreateTable(Type type)
        {
            var table = MetadataBuider.TableDataFor(type);

            if (_dbType == DbToTest.Postgres || _dbType == DbToTest.MySql)
            {
                DB.Execute(string.Format("drop table if exists {0}", table.Name.Escaped));
            }
            // SQLite and SqlServer should alredy be clean at this point, no need to drop

            var sql = new StringBuilder();
            sql.AppendFormat("create table {0} (", table.Name.Escaped).AppendLine();

            string comma = "";
            foreach (var column in table.Columns)
            {
                string identity = "";
                string sqlType = dialect.GetSqlTypeName(column.SqlType);

                if (table.OptionalData.HasAutoIncrementPK
                    && table.OptionalData.PrimaryKey == column)
                {
                    if (_dbType == DbToTest.SqlServer)
                    {
                        identity = "primary key identity";
                    }
                    if (_dbType == DbToTest.MySql)
                    {
                        identity = "primary key auto_increment";
                    }
                    if (_dbType == DbToTest.Sqlite)
                    {
                        identity = "primary key autoincrement";
                        sqlType = "integer";
                    }
                    if (_dbType == DbToTest.Postgres)
                    {
                        identity = "serial";
                        sqlType = "";
                    }
                }

                sql.AppendFormat("  {0}{1} {2} {3}", comma, column.Name.Escaped, sqlType, identity).AppendLine();
                comma = ", ";
            }

            sql.AppendLine(");");

            string createTable = sql.ToString();
            DB.Execute(createTable);
        }
    }
}
